# coding=utf-8
import json
import uuid
from concurrent.futures import ThreadPoolExecutor
from datetime import datetime
import pandas as pd
from flask.blueprints import Blueprint
from flask import request, render_template, current_app
from threading import Thread
from src.api.response.LayR import LayR
from src.api.response.R import R
from src.db.dbhelper import query_sql_by_pandas, df_to_model_list, execute_sql
from src.db.flaskdb import add_model, del_model, del_models
from src.model.dynamic_remain_material import DynamicRemainMaterial
from src.model.material_plan import MaterialPlan
from src.model.material_plan_other import MaterialPlanOther
from src.service.planservice import execute_plan, execute_dynamic_plan
from src.util.pathutil import get_export_dir

plan_api = Blueprint('plan_api', __name__, url_prefix="/api/plan")
executor = ThreadPoolExecutor(2)


@plan_api.route('/plan_manager_page', methods=['GET'])
def plan_manager_page():
    return render_template("/plan/plan_manager.html")


@plan_api.route("/plan_result_manager", methods=['GET'])
def plan_result_manager():
    sql = "select id, plan_name from material_plan where is_del = 0 and is_finish = 2 "
    df = query_sql_by_pandas(sql)
    plan_dic = dict(zip(df["id"], df["plan_name"]))
    return render_template("/plan/plan_result_manager.html", plan_dic=plan_dic)


@plan_api.route("/plan_other_manager", methods=["GET"])
def plan_other_manager():
    sql = "select id, plan_name from material_plan where is_del = 0 and is_finish = 2 "
    df = query_sql_by_pandas(sql)
    plan_dic = dict(zip(df["id"], df["plan_name"]))
    return render_template("/plan/plan_other_manager.html", plan_dic=plan_dic)


@plan_api.route("/plan_other_list", methods=["POST"])
def plan_other_list():
    plan_id = request.form.get("plan_id")
    page = int(request.form.get("page"))
    limit = int(request.form.get("limit"))
    r = LayR()
    paginate = None
    if plan_id is None or plan_id == "":
        paginate = MaterialPlanOther.query
    else:
        paginate = MaterialPlanOther.query.filter(MaterialPlanOther.plan_id == plan_id)
    pagination = paginate.paginate(page=int(page), per_page=int(limit))
    items = pagination.items
    total = pagination.total
    lis = []
    for item in items:
        lis.append(item.to_dict())
    r.code = 0
    r.msg = "ok"
    r.data = lis
    r.count = total
    return {
        "code": 0,
        "data": lis,
        "count": total,
        "msg": "ok"
    }


@plan_api.route("/get_header", methods=['POST'])
def get_header():
    plan_id = request.form.get("plan_id")
    plan = MaterialPlan.query.filter(MaterialPlan.id == plan_id).filter(
        MaterialPlan.is_del == 0).first()
    if plan is None:
        return {"code": 500, "msg": "表头为空"}
    sql = f"select * from result_{plan.id} "
    df = query_sql_by_pandas(sql)
    headers = df.columns.tolist()
    headers = [str(head) for head in headers]
    material_sql = "select id, name from material_type where is_del = 0 "
    header_df = query_sql_by_pandas(material_sql)
    header_dic = dict(zip(header_df["id"], header_df["name"]))
    result_header = [{
        "key": "product_model",
        "val": "机型",
        "totalRowText": "合计"
    }, {
        "key": "total",
        "val": "可建造数量",
        "totalRowText": "0"
    }, {
        "key": "e_sort",
        "val": "执行顺序",
        "totalRowText": "--"
    }]
    sql = f"select * from result_{plan_id} order by total desc "
    df = query_sql_by_pandas(sql)
    df.drop('product_model', axis=1, inplace=True)
    sum_df = df.sum()
    for key in header_dic.keys():
        if str(key) in headers:
            va = header_dic.get(key)
            result_header.append({
                "key": str(key),
                "val": f"材料-{va}-消耗量",
                "totalRowText": '0'
            })
    for item in result_header:
        key = item.get("key")
        val = item.get("val")
        if key == "product_model" or key == "e_sort":
            continue
        s_val = sum_df.loc[key]
        item["totalRowText"] = str(s_val)
    return result_header


@plan_api.route("/get_plan_result_list", methods=['POST'])
def get_plan_result_list():
    r = LayR()
    limit = int(request.form.get("limit"))
    page = int(request.form.get("page"))
    plan_id = request.form.get("plan_id")
    # plan = MaterialPlan.query.filter(MaterialPlan.plan_name.like(f"%{plan_name}%")).filter(
    #     MaterialPlan.is_del == 0).first()
    # if plan is None:
    #     return r.to_dict()
    if plan_id is None or plan_id == "":
        return r.to_dict()
    sql = f"select * from result_{plan_id} order by total desc "
    df = query_sql_by_pandas(sql)
    start = (page - 1) * limit
    end = limit * page
    total = len(df)
    df = df[start:end]
    lis = df_to_model_list(df)
    r.code = 0
    r.msg = "ok"
    r.data = lis
    r.count = total
    return {
        "code": 0,
        "data": lis,
        "count": r.count,
        "msg": "ok"
    }


@plan_api.route("/export_result", methods=['POST'])
def export_result():
    r = R()
    plan_id = request.form.get("plan_id")
    sql = f"select * from result_{plan_id} order by total desc "
    material_sql = "select name, id from material_type where is_del = 0 "
    material_dic = None
    material_df = None
    try:
        df = query_sql_by_pandas(sql)
        material_df = query_sql_by_pandas(material_sql)
        material_df["id"] = material_df["id"].astype(str)
        material_dic = dict(zip(material_df["id"], material_df["name"]))
    except Exception as e:
        df = pd.DataFrame()
    if df.empty:
        r.code = 500
        r.msg = "未查询到数据"
        return r.to_dict()
    real_columns = []
    material_dic.setdefault("total", "可制造总数")
    material_dic.setdefault("finish", "是否计算完成")
    material_dic.setdefault("plan_id", "计划编号")
    material_dic.setdefault("plan_name", "计划名称")
    material_dic.setdefault("product_model", "型 号")
    material_dic.setdefault("e_sort", "执行顺序")
    cols = df.columns.tolist()
    if "finish" in cols:
        df.drop(["finish"], axis=1, inplace=True)
    if "plan_id" in cols:
        df.drop(["plan_id"], axis=1, inplace=True)
    columns = df.columns.tolist()
    for column in columns:
        col = material_dic.get(str(column))
        real_columns.append(col)
    df.columns = real_columns
    file_id = str(uuid.uuid4()).replace("-", "")
    file_name = f"{file_id}.xlsx"
    file_path = get_export_dir().joinpath(file_name)
    export_name = f'{df.iloc[0]["计划名称"]}.xlsx'
    df.to_excel(file_path, index=False)
    r.code = 200
    r.msg = "文件生成成功"
    r.data = {
        "file_name": file_name,
        "export_name": export_name
    }
    return r.to_dict()


@plan_api.route("/get_plan_list", methods=["POST"])
def get_plan_list():
    r = LayR()
    limit = request.form.get("limit")
    page = request.form.get("page")
    plan_name = request.form.get("plan_name")
    paginate = None
    if plan_name is None or plan_name == "":
        paginate = MaterialPlan.query
    else:
        paginate = MaterialPlan.query.filter(MaterialPlan.plan_name.like(f"%{plan_name}%"))
    pagination = paginate.paginate(page=int(page), per_page=int(limit))
    items = pagination.items
    total = pagination.total
    lis = []
    for item in items:
        lis.append(item.to_dict())
    r.code = 0
    r.msg = "ok"
    r.data = lis
    r.count = total
    return {
        "code": 0,
        "data": lis,
        "count": total,
        "msg": "ok"
    }


@plan_api.route("/add_plan", methods=["POST"])
def add_plan():
    plan_name = request.form.get("plan_name")
    start_time = request.form.get("start_time")
    end_time = request.form.get("end_time")
    plan_type = request.form.get("plan_type")

    if plan_name is None or plan_name == "":
        return {
            "code": 500,
            "msg": "计划名称为空"
        }
    if start_time is None or start_time == "":
        return {
            "code": 500,
            "msg": "计划开始时间为空"
        }
    if end_time is None or end_time == "":
        return {
            "code": 500,
            "msg": "计划结束时间为空"
        }
    first = MaterialPlan.query.filter(MaterialPlan.plan_name == plan_name).filter(MaterialPlan.is_del == 0).first()
    if first is None:
        # 新增
        plan = MaterialPlan()
        plan.plan_name = plan_name
        plan.start_time = datetime.strptime(start_time, "%Y-%m-%d")
        plan.end_time = datetime.strptime(end_time, "%Y-%m-%d")
        plan.create_time = datetime.now()
        plan.plan_type = int(plan_type)
        plan.remark = ""
        plan.is_del = 0
        add_model(plan)
        if int(plan_type) == 2:
            # 如果是新增动态执行计划
            # 获取当前所有的剩余材料信息
            material_sql = "select material_id , material_name , material_quantity from material_type_quantity mtq where mtq.is_del  = 0"
            all_materials_df = query_sql_by_pandas(material_sql)
            # 将资产转换为字典方便后续使用
            material_all_dict_source = dict(zip(all_materials_df["material_id"], all_materials_df["material_quantity"]))
            dynamic_remain_material_mo = DynamicRemainMaterial()
            dynamic_remain_material_mo.plan_id = plan.id
            dynamic_remain_material_mo.material_remain = json.dumps(material_all_dict_source, indent=4)
            dynamic_remain_material_mo.is_del = 0
            dynamic_remain_material_mo.create_time = datetime.now()
            dynamic_remain_material_mo.execute_product = "执行型号的顺序为:"
            add_model(dynamic_remain_material_mo)
        return {
            "code": 200,
            "msg": "新增成功"
        }
    else:
        return {
            "code": 500,
            "msg": "当前计划名称已经存在"
        }


@plan_api.route("/del_plan", methods=["POST"])
def del_plan():
    plan_id = request.form.get("id")
    first = MaterialPlan.query.filter(MaterialPlan.id == plan_id).first()
    if first is not None:
        del_model(first)
        MaterialPlanOther.query.filter(MaterialPlanOther.plan_id == plan_id).delete()
        try:
            drop_sql = f" drop table result_{plan_id} "
            execute_sql(drop_sql)
        except Exception as e:
            pass
    return {
        "code": 200,
        "msg": "删除成功"
    }


@plan_api.route("/execute_plan", methods=["POST"])
def run_plan():
    plain_id = request.form.get("plan_id")
    r = R()
    plan = MaterialPlan.query.filter(MaterialPlan.id == plain_id).first()
    if plan is None:
        r.code = 500
        r.msg = "计划未找到"
        return r.to_dict()
    context = current_app._get_current_object()
    request_body = {
        "current_object": context,
        "plan": plan
    }
    executor.submit(execute_plan, request_body=request_body)
    r.code = 200
    r.msg = "计划执行成功"
    return r.to_dict()


@plan_api.route("/execute_dynamic_plan", methods=["POST"])
def run_dynamic_plan():
    plain_id = request.form.get("plan_id")
    product_models = request.form.get("product_models")
    print(product_models)
    r = R()
    if product_models is None or product_models == "":
        return r.setResponse(code=500, msg="请先选择要执行的型号").to_dict()
    product_model = product_models.split(",")
    product_model.remove("")
    plan = MaterialPlan.query.filter(MaterialPlan.id == plain_id).first()
    if plan is None:
        r.code = 500
        r.msg = "计划未找到"
        return r.to_dict()
    context = current_app._get_current_object()
    request_body = {
        "current_object": context,
        "plan": plan,
        "product_models": product_model
    }
    executor.submit(execute_dynamic_plan, request_body=request_body)
    r.code = 200
    r.msg = "计划执行成功"
    return r.to_dict()


@plan_api.route("/get_dynamic_execute_result", methods=["POST"])
def get_dynamic_execute_result():
    plain_id = request.form.get("plan_id")
    page = int(request.form.get("page"))
    limit = int(request.form.get("limit"))
    r = R()
    try:
        sql = f"select product_model, e_sort, total from result_{plain_id}"
        df = query_sql_by_pandas(sql)
    except Exception as e:
        return {
            "code": 0,
            "data": [],
            "count": 0,
            "msg": "ok"
        }
    start = (page - 1) * limit
    end = limit * page
    total = len(df)
    df = df[start:end]
    lis = df_to_model_list(df)
    r.code = 0
    r.msg = "ok"
    r.data = lis
    r.count = total
    return {
        "code": 0,
        "data": lis,
        "count": r.count,
        "msg": "ok"
    }


@plan_api.route("/export_dynamic_result", methods=["POST"])
def export_dynamic_result():
    plain_id = request.form.get("plan_id")
    r = R()
    sql = f"select product_model, e_sort, total, plan_name from result_{plain_id}"
    df = query_sql_by_pandas(sql)
    df.columns = ["执行顺序", "型号", "可建造总数", "计划名称"]
    file_id = str(uuid.uuid4()).replace("-", "")
    file_name = f"{file_id}.xlsx"
    file_path = get_export_dir().joinpath(file_name)
    export_name = f'{df.iloc[0]["计划名称"]}.xlsx'
    df.to_excel(file_path, index=False)
    r.code = 200
    r.msg = "文件生成成功"
    r.data = {
        "file_name": file_name,
        "export_name": export_name
    }
    return r.to_dict()
